package com.book.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.book.dto.DataDto;
import com.book.entity.Order;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

//数据统计用到的表和订单表是一个，注意，所有统计都应该把已取消排除在外，即status为3的数据
@Mapper
public interface DataDao extends BaseMapper<Order> {

    //按照月份和年份来统计销售金额
    @Select("SELECT\n" +
            "    DATE_FORMAT(order_time, '%Y-%m') AS showTime,\n" +
            "    SUM(price) AS totalPrice\n" +
            "FROM\n" +
            "    order_form\n" +
            "WHERE\n" +
            "    YEAR(order_time)=#{year}\n" +
            "GROUP BY\n" +
            "    showTime\n" +
            "ORDER BY\n" +
            "    showTime;")
    List<DataDto> getPriceByMonth(@Param("year") int year);

    //按照季度来统计销售金额
    @Select("SELECT     \n" +
            "    CONCAT(DATE_FORMAT(order_time, '%Y'), '-第', QUARTER(order_time),'季度') AS showTime,\n" +
            "    SUM(price) AS totalPrice\n" +
            "FROM\n" +
            "    order_form\n" +
            "WHERE     \n" +
            "    YEAR(order_time)=#{year}\n" +
            "GROUP BY     \n" +
            "    showTime    \n" +
            "ORDER BY     \n" +
            "    showTime;")
    List<DataDto> getPriceByQuarter(@Param("year") int year);

    //相同年月统计销售数量前10的书籍
    @Select("SELECT   \n" +
            "    a.book_id as bookId,\n" +
            "   (select book_name from book where id=a.book_id) as bookName,\n" +
            "    SUM(a.amount) AS totalAmount\n" +
            "FROM   \n" +
            "    order_form a\n" +
            "WHERE   \n" +
            "    YEAR(a.order_time)=#{year} AND MONTH(a.order_time)=#{month} -- 使用传入的年份和月份进行比较  \n" +
            "GROUP BY   \n" +
            "    bookId  \n" +
            "ORDER BY   \n" +
            "    totalAmount DESC  \n" +
            "LIMIT 10;")
    List<DataDto> getAmountSameDate(@Param("year") String year,@Param("month") String month);

    //统计不同年月统计销售数量前10的书籍，注意这里有四个参数
    @Select("SELECT   \n" +
            "    a.book_id as bookId,\n" +
            "   (select book_name from book where id=a.book_id) as bookName,\n" +
            "    SUM(a.amount) AS totalAmount\n" +
            "FROM\n" +
            "    order_form a\n" +
            "WHERE\n" +
            "    a.order_time BETWEEN STR_TO_DATE(CONCAT(#{startYear}, '-', #{startMonth}, '-01'), '%Y-%m-%d')\n" +
            "    AND LAST_DAY(STR_TO_DATE(CONCAT(#{endYear}, '-', #{endMonth}, '-31'), '%Y-%m-%d'))\n" +
            "GROUP BY\n" +
            "    bookId\n" +
            "ORDER BY\n" +
            "    totalAmount DESC\n" +
            "LIMIT 10;")
    List<DataDto> getAmountNoSameDate(@Param("startYear") String startYear,@Param("startMonth") String startMonth,
                                      @Param("endYear") String endYear,@Param("endMonth") String endMonth);
}
